package cc.messcat.service.fupin;

import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

import cc.messcat.common.model.Pager;
import cc.messcat.common.service.BaseServiceImpl;
import cc.messcat.dao.fupin.FamilyDao;
import cc.messcat.dao.fupin.VisitRecdDao;
import cc.messcat.entity.fupin.Family;
import cc.messcat.entity.fupin.VisitRecd;
import cc.messcat.entity.system.McUser;
import cc.messcat.util.ExcelUtil;

@Service
public class VisitRecdServiceImpl extends BaseServiceImpl<VisitRecd> implements VisitRecdService {

	@Autowired
	private VisitRecdDao visitRecdDao;
	@Autowired
	private FamilyDao familyDao;
	

	@SuppressWarnings("unchecked")
	@Override
	public Pager<VisitRecd> retrieveVisitRecdPager(Integer pageNo, Integer pageSize, VisitRecd visitRecd,String beginDate, String endDate) {
		Map map = new HashMap<>();
		 String date = "";
		if (StringUtils.isNotBlank(beginDate) && StringUtils.isNotBlank(endDate)) 
			date = " and (addTime between '" + beginDate + "' and '" + endDate + "') ";
			String hql = "from VisitRecd where isDel= :isDel and poorFamily.id=:poorFamilyId " + date
					+ "  ORDER BY addTime DESC";
			map.put("isDel", "0");
			map.put("poorFamilyId", visitRecd.getPoorFamily().getId());
			return this.visitRecdDao.getPagerByHql(pageNo, pageSize, hql, map);
		}

	
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportVisitRecd(Integer pageNo, Integer pageSize, VisitRecd visitRecd,String beginDate, String endDate,HttpServletResponse response) {
		
		String[] cellName = new String[]
				{"序号","帮扶时间","帮扶贫困户户主","帮扶目的","描述","备注"};
		String[] field = new String[] 
				{"addTime","householder","target","content","remarks"};
		
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;
			pager  = retrieveVisitRecdPager(sheetCount,5000,visitRecd, beginDate, endDate);
			
	        List<Object> list0 = new ArrayList<Object>();
	 		List<VisitRecd> vs = pager.getResultList();
	 		for (int i = 0; i < vs.size(); i++) {
	 			Map<String, Object> map=null;
	 			if (vs.get(i) != null) {
	 				 map = new HashMap<String, Object>();
	 				map.put("remarks", vs.get(i).getRemarks());
	 				map.put("target", vs.get(i).getTarget());
	 				map.put("content", vs.get(i).getContent());
	 				map.put("householder", "");
	 				map.put("addTime", vs.get(i).getAddTime() != null? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(vs.get(i).getAddTime()) : "");
	 				for(Family f : vs.get(i).getPoorFamily().getFamily()) {
	 					if("0".equals(f.getIsDel()) && "户主".equals(f.getRelation())) {
	 						map.put("householder", f.getName());
	 					}
	 				}
	 			}
	 			list0.add(map);
	 		}
			
			excelResult.put(sheetCount, list0);
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		
		List<Family> list = queryHZ(visitRecd);
		String hzName = "";
         if(list!=null && list.size()>0 ){
        	 hzName = StringUtils.isNotBlank(list.get(0).getName())?list.get(0).getName():"";
        	 hzName+="_";
         }
		
		//名字为贫困户户主姓名_帮扶记录.xls
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet(hzName+"帮扶记录" + i);
			sheet.setDefaultColumnWidth(36);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet,cellName,hzName+"帮扶记录",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		try {
			response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(hzName+"帮扶记录","UTF-8")+".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		
		return workbook;
	}

	/**
	 * 户主查询
	 * 
	 * @param visitRecd
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Family> queryHZ(VisitRecd visitRecd) {
		String hql = "from Family where   relation = '户主'  and fid =:fid  and  annual =:annual";
		Map map = new HashMap<>();
		map.put("fid", visitRecd.getPoorFamily().getId());
		map.put("annual", new SimpleDateFormat("YYYY").format(new Date()));

		List<Family> list = this.familyDao.queryList(hql, map);
		return list;
	}


	@Override
	@SuppressWarnings("unchecked")
	public List<VisitRecd> getRetrieveByPID(McUser user,Long id) throws Exception {
		
		try {
			return visitRecdDao.find("from VisitRecd where isDel=0 and pid="+id);
			
		} catch (org.hibernate.ObjectNotFoundException e) {
			//如果没有找到外键关联的户主
			//return visitRecdDao.getRetrieveByPID(user,id);
			throw new RuntimeException(e);
		}
	}


	@Override
	public Long addVisitRecd(VisitRecd visitRecd) {
		return visitRecdDao.addVisitRecd(visitRecd);
	}


	@Override
	public Long getCountByPid(Long id) {
		try {
			//visitRecdDao.find("select count(*) from VisitRecd where isDel=0 and pid="+id);
			Long count = visitRecdDao.getCountByPid(id);
			return count;
		} catch (org.hibernate.ObjectNotFoundException e) {
			//如果没有找到外键关联的户主
			//return visitRecdDao.getRetrieveByPID(user,id);
			throw new RuntimeException(e);
		}
	}
	

}
